from openpyxl import load_workbook
from pathlib import Path
from rich import print as rprint

from sys import path
orign_path = 'd:/python&work'
path.append(orign_path)
from little_function import read, convert

def fill_in(file_path, programme_name: str, sheet_name: str, header_info: dict, need_change_columns: list, manual_change_columns: list,
            result_in_yuan: bool = True, added_clear_columns: list = []):
    '''
    - programme_name: 项目名称
    - sheet_name: 需要修改的表的sheet名
    - header_info: 需要修改的表的表头信息，ex: {'start_row': 3, 'end_row': 4}
    - need_change_columns: 需要填充的列名
    - manual_change_columns: 需要手动填充的列名
    '''
    rprint(f'[bold green]开始处理 {programme_name}')
    file_type = file_path.stem.split('-')[1]
    try:
        ledger_file = ledger_files[programme_name]
    except KeyError:
        rprint(f'[red]{programme_name} 对应的台账文件不存在，跳过')
        return
    ledger_content = read.read_ledger(ledger_file)
    ws = wb[sheet_name]
    header_start_row = header_info['start_row']
    headers_info = [(cell.column, cell.value) for cell in ws[header_start_row]]
    for the_cell in ws['A']:        # 探索主要内容有多少行。必须有“合计”行
        if the_cell.value == '合计':
            last_content_row = the_cell.row - 1
            break
    # 删除填充列的内容
    for col_name in need_change_columns:
        try:
            col_index = [t[0] for t in headers_info if t[1] == col_name][0]
        except:
            rprint(f'[yellow]{sheet_name} {file_type} {col_name} 此列需清空，未找到该列')
            continue
        header_end_row = header_info['end_row']
        for row_index in range(header_end_row+1, last_content_row + 1):
            ws.cell(row=row_index, column=col_index).value = None
    # 开始填充值
    auto_change_columns = need_change_columns[:]        # 复制一下list，否则会改变全局变量
    for col in manual_change_columns:
        auto_change_columns.remove(col)        # 剔除需要手动填充的列
    header_end_row = header_info['end_row']
    for row_index in range(header_end_row+1, last_content_row + 1):
        for col_name in auto_change_columns:
            col_index = col_index = [t[0] for t in headers_info if t[1] == col_name][0]
            unit_col = [t[0] for t in headers_info if t[1] == '往来单位名称'][0]
            the_cell = ws.cell(row=row_index, column=unit_col)
            unit_name = the_cell.value
            if unit_name == None:
                rprint(f'[yellow]{sheet_name} {file_type} 中往来单位单元格 {the_cell.coordinate} 为空，跳过')
                break
            elif unit_name not in ledger_content:
                rprint(f'[yellow]{sheet_name} {file_type} 中的 {unit_name} 未在结算支付台账中找到，请检查！')
                break
            the_value = ledger_content[unit_name][col_name]
            try:
                the_value = round(float(the_value), 2)
            except ValueError:          # 单元格内容为文本
                pass
            else:
                if not result_in_yuan:       # 金额单位为万元，需转换为元
                    the_value = convert.convert_to_wan(the_value)
            ws.cell(row_index, col_index).value = the_value
        else:
            ledger_content.pop(unit_name)
    wb.save(file_path)       # 每个工作表填充完毕后，保存一下

root_path = Path('d:/copy&run')

ledger_root = root_path / '结算支付台账'
ledger_files = read.get_files(ledger_root)

file_type = '资金计划'
file_type = '借款报告明细表'

if file_type == '借款报告明细表':
    # 需要先修改明细表中的往来单位名称，再运行程序填充结算及付款数据
    header_info = {'start_row': 4, 'end_row': 4}
    need_change_columns = ['款项性质', '结算金额（含税）', '应付结算款', '实际已付款', '剩余应付款', '本期借款', '备注']
    manual_change_columns = ['本期借款', '备注']

    loan_report_path = root_path / '临朐项目-借款报告明细表-2024年12月.xlsx'
    wb = load_workbook(loan_report_path)            # 打开借款报告明细表
    sheet_names = wb.sheetnames
    for sheet_name in sheet_names:
        fill_in(loan_report_path, sheet_name, sheet_name, header_info, need_change_columns, manual_change_columns)
elif file_type == '资金计划':
    header_info = {'start_row': 3, 'end_row': 4}
    need_change_columns = ['支付方式', '结算金额（不含税）', '结算税金', '质量保证金', '民工工资保证金', '已付结算款', '一年期付款合计', '本月预计结算', '备注']
    manual_change_columns = ['支付方式', '本月预计结算', '备注']

    funding_plan_root = root_path / '资金计划'
    funding_plan_files = read.get_files(funding_plan_root)
    for progromme_name, funding_plan_file in funding_plan_files.items():
        wb = load_workbook(funding_plan_file)
        added_clear_columns = []
        fill_in(funding_plan_file, progromme_name, '项目明细表', header_info, need_change_columns, manual_change_columns, result_in_yuan=False)

